﻿<%@ WebHandler Language="C#" Class="CommisionDetail" %>


using System;
using System.Web;
using System.Text;
using System.Collections.Generic;
using System.Data;
using Chenxin.Lib.Utility;
using Chenxin.Lib.Business;
using Chenxin.Lib.Business.BLL;

public class CommisionDetail : AjaxHandlerBase
{
    public CommisionDetail()
    {
        //导出
        htTypePermit.Add("export", Constants.Permit_CommisionDetail_Export);
        htTypePermit.Add("exportall", Constants.Permit_CommisionDetail_Export);
    }
    override public void RequestProcess(HttpContext context)
    {
        try
        {
            context.Response.ContentType = "text/plain";
            context.Response.AddHeader("pragma", "no-cache");
            context.Response.AddHeader("cache-control", "");
            context.Response.CacheControl = "no-cache";

            string strOp = context.Request["op"];
            string strRet = string.Empty;
            switch (strOp.ToLower())
            {
                //添加报备相关信息
                case "getlist":
                    GetList(context);
                    break;
                case "exportall":
                    ExportAll(context);
                    break;
                default:
                    break;
            }
        }
        catch (Exception ex)
        {
            context.Response.Write(retExecResult(Constants.ExecResult.Error, ex.Message, ""));
        }
    }

    //导出全部的方法
    private void ExportAll(HttpContext context)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append(" (1=1) and Status=1 ");        

        //查询条件
        //关键字
        if (!string.IsNullOrEmpty(context.Request["keyword"]))
            sb.AppendFormat(" and (ContractCode like '%{0}%' or HouseAddress like '%{0}%') ", context.Request["keyword"]);
        //归属日期
        if (!string.IsNullOrEmpty(context.Request["datetype"]))
        {
            string sdate = "";
            string edate = "";
            if (!string.IsNullOrEmpty(context.Request["ArcTimes"]))
                sdate = context.Request["ArcTimes"];
            if (!string.IsNullOrEmpty(context.Request["ArcTimee"]))
                edate = context.Request["ArcTimee"];

            //实收
            if (context.Request["datetype"] == "PayArchDate")
            {
                if (!string.IsNullOrEmpty(sdate) && !string.IsNullOrEmpty(edate))
                {
                    sb.AppendFormat(" and  EXISTS(select 1 from t_Contract_Finance_BillInfo F where a.ContractId = F.ContractId and cast(case when len(ISNULL(f.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(f.AchievementDate, '1900.1') end +'.1' as datetime) between '{0}' AND '{1}') ",
                        DateTime.Parse(sdate), DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                }
                else if (!string.IsNullOrEmpty(sdate))
                {
                    sb.AppendFormat(" and  EXISTS(select 1 from t_Contract_Finance_BillInfo F where a.ContractId = F.ContractId and cast(case when len(ISNULL(f.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(f.AchievementDate, '1900.1') end +'.1' as datetime) >= '{0}' ) ",
                        DateTime.Parse(sdate));
                }
                else if (!string.IsNullOrEmpty(edate))
                {
                    sb.AppendFormat(" and  EXISTS(select 1 from t_Contract_Finance_BillInfo F where a.ContractId = F.ContractId and cast(case when len(ISNULL(f.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(f.AchievementDate, '1900.1') end +'.1' as datetime) < '{0}' ) ",
                        DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                }
            }
            //业绩
            else
            {
                if (!string.IsNullOrEmpty(sdate) && !string.IsNullOrEmpty(edate))
                {
                    sb.AppendFormat(" and (ArchivementDate between '{0}' AND '{1}') ",
                        DateTime.Parse(sdate), DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                }
                else if (!string.IsNullOrEmpty(sdate))
                {
                    sb.AppendFormat(" and  (ArchivementDate >= '{0}' ) ",
                        DateTime.Parse(sdate));
                }
                else if (!string.IsNullOrEmpty(edate))
                {
                    sb.AppendFormat(" and  (ArchivementDate < '{0}' ) ",
                       DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                }
            }
        }
        //成交日期
        if (!string.IsNullOrEmpty(context.Request["starttime"]))
            sb.AppendFormat(" and a.ContractDate>='{0}' ", context.Request["starttime"]);
        if (!string.IsNullOrEmpty(context.Request["endtime"]))
            sb.AppendFormat(" and a.ContractDate<='{0}' ", context.Request["endtime"]);
        //归属公司
        if (!string.IsNullOrEmpty(context.Request["BargainCompanyId"]))
            sb.AppendFormat(" and BargainCompanyId='{0}' ", context.Request["BargainCompanyId"]);
        //状态
        if (!string.IsNullOrEmpty(context.Request["status"]))
        {
            if (context.Request["status"] == "10")
            {//实收截止
                if (!string.IsNullOrEmpty(context.Request["overtime"]))
                    sb.AppendFormat(" and bArchivement=1 and ((VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal)>((dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}'))+(dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}')))) ", context.Request["overtime"]);
                else
                    sb.Append(" and bArchivement=1 and (VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal) > (VCommisionGeted+BCommisionGeted)");
            }
            else if (context.Request["status"] == "20")
            {//实收截止
                if (!string.IsNullOrEmpty(context.Request["overtime"]))
                    sb.AppendFormat(" and bArchivement=1 and ((VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal)<=dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}')+dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}'))  ", context.Request["overtime"]);
                else
                    sb.Append(" and bArchivement=1 and ((VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal)<= VCommisionGeted+BCommisionGeted)");
            }
            else if (context.Request["status"] == "30")
                sb.Append(" and bArchivement=0 ");
        }
        else
            sb.Append(" and bArchivement=1 and (VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal > VCommisionGeted+BCommisionGeted)");


        //查询5级
        Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
        Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
        Guid businessdistrict = (string.IsNullOrEmpty(context.Request["areaid"])) ? Guid.Empty : new Guid(context.Request["areaid"]);
        Guid departs = (string.IsNullOrEmpty(context.Request["departid"])) ? Guid.Empty : new Guid(context.Request["departid"]);
        Guid userId = (string.IsNullOrEmpty(context.Request["UserId"])) ? Guid.Empty : new Guid(context.Request["UserId"]);

        if (userId != Guid.Empty)
            sb.AppendFormat(" AND (a.contractid in (select ContractId from t_Contract_PointAllotInfo where EmployeeId='{0}')) ", userId);
        if (departs != Guid.Empty)
        {
            sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where DepartId='{0}'))", departs);
        }
        else if (businessdistrict != Guid.Empty)
        {
            sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo "+
            "where DepartId in (Select ChildId from v_Hr_Struct_DepartTreeRelation where ParentId ='{0}')))",
                businessdistrict);
        }
        else if (company != Guid.Empty)
        {
            sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.companyid='{0}')))", company);
        }
        else if (city != Guid.Empty)
        {
            sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.CityId='{0}'))) ", city);
        }
        else
        {
            sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.companyid='{0}'))) ", CurrentUserInfo.CompanyId);
        }



        //条件
        string strWhere = sb.ToString();

        StringBuilder sbTableFieldList = new StringBuilder();
        sbTableFieldList.Append("a.ContractId,dbo.FN_GetBussinessByDepartID(BuyerDepartId) as business,BuyerDepartName,");
        sbTableFieldList.Append("CONVERT(Varchar(10),a.ContractDate,102) as ContractDate,ContractCode,ConfirmEmplyee,CreateArchEmp,HouseAddress,VSTDiscountFinal,BSTDiscountFinal,");
        sbTableFieldList.Append("dbo.f_get_contract_pointallot('Depart',a.ContractId,'10') AS VDepartName," +
                "dbo.f_get_contract_pointallot('Employee',a.ContractId,'10') as VUserName," +
                "dbo.f_get_contract_pointallot('Point',a.ContractId,'10') as V_Points," +
                "dbo.f_get_contract_pointallot('Depart',a.ContractId,'20') AS BDepartName," +
                "dbo.f_get_contract_pointallot('Employee',a.ContractId,'20') as BUserName," +
                "dbo.f_get_contract_pointallot('Point',a.ContractId,'20') as B_Points,");

        sbTableFieldList.Append("VStanCommision,BStanCommision,VRealCommisionFinal,BRealCommisionFinal,");
        sbTableFieldList.Append("VCommisionFinal,BCommisionFinal,VConsultingFinal,BConsultingFinal,");
        sbTableFieldList.AppendFormat("dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}') as VCommisionGeted,dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}') as BCommisionGeted,(VRealCommisionFinal-VSTDiscountFinal-dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}')) as VUnGeted,", string.IsNullOrEmpty(context.Request["overtime"]) ? "" : context.Request["overtime"]);
        sbTableFieldList.AppendFormat("(BRealCommisionFinal-BSTDiscountFinal-dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}')) as BUnGeted,", string.IsNullOrEmpty(context.Request["overtime"]) ? "" : context.Request["overtime"]);
        sbTableFieldList.Append("DATEDIFF(d,DealDate,getdate()) as DealDate,");
        sbTableFieldList.Append("Convert(varchar(10),ArchivementDate,102) as ArchivementDate, ");
        sbTableFieldList.Append("dbo.f_getRemarkInfo(a.ContractId) as HasFollow,BargainCompanyName");
        
        //所要查询的表名
        string TableName = "t_Contract_BaseInfo a inner join t_Contract_CommisionInfo b On A.ContractId = b.ContractId left join t_Contract_Infomation c on a.contractId = c.contractId ";
        //排序
        string strOrderField = "a.ContractDate desc";
        if (!string.IsNullOrEmpty(context.Request["sort"]))
            strOrderField = context.Request["sort"];

        string strSql = "select " + sbTableFieldList.ToString() + " from " + TableName + " where " + strWhere + " order by " + strOrderField;
        //根据sql执行查询
        DataSet ds = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);

        string strHTML = GetHtml(ds.Tables[0]);

        ExportByServce(context, strHTML);        
    }

    //导出全部的HTML
    private string GetHtml(DataTable dt)
    {
        StringBuilder sb = new StringBuilder();
        sb.Append("<table class=\"tabsorternew table_archive\"><tr>");
        sb.Append("<th style=\"width:120px;\" rowspan=\"2\">商圈</th>");
        sb.Append("<th style=\"width:120px;\" rowspan=\"2\">门店</th>");
        sb.Append("<th style=\"width:80px;\" rowspan=\"2\">成交日期</th>");
        sb.Append("<th style=\"width:130px;\" rowspan=\"2\">成交编号</th>");
        sb.Append("<th style=\"width:300px;\" rowspan=\"2\">地址</th>");
        sb.Append("<th style=\"width:120px;\" rowspan=\"2\">编号发放人员</th>");
        sb.Append("<th style=\"width:120px;\" rowspan=\"2\">业绩确认人员</th>");
        sb.Append("<th style=\"width:180px;\">上家门店</th>");
        sb.Append("<th style=\"width:180px;\">上家业务员</th>");
        sb.Append("<th style=\"width:110px;\">上家标准佣收</th>");
        sb.Append("<th style=\"width:100px;\">上家确认佣收</th>");
        sb.Append("<th style=\"width:100px;\">上家折佣</th>");
        sb.Append("<th style=\"width:100px;\">上家咨询费</th>");
        sb.Append("<th style=\"width:100px;\">上家特殊折佣</th>");
        sb.Append("<th style=\"width:100px;\">上家实收</th>");
        sb.Append("<th style=\"width:100px;\">上家未收</th>");
        sb.Append("<th style=\"width:100px;\" rowspan=\"2\">账龄</th>");
        sb.Append("<th style=\"width:100px;\" rowspan=\"2\">业绩归属时间</th>");
        sb.Append("<th style=\"width:100px;\" rowspan=\"2\">是否有未跟进</th>");
        sb.Append("<th style=\"width:100px;\" rowspan=\"2\">合同归属公司</th></tr>");
        sb.Append("<tr><th style=\"width:180px;\">下家门店</th>");
        sb.Append("<th style=\"width:180px;\">下家业务员</th>");
        sb.Append("<th style=\"width:110px;\">下家标准佣收</th>");
        sb.Append("<th style=\"width:100px;\">下家确认佣收</th>");
        sb.Append("<th style=\"width:100px;\">下家折佣</th>");
        sb.Append("<th style=\"width:100px;\">下家咨询费</th>");
        sb.Append("<th style=\"width:100px;\">下家特殊折佣</th>");
        sb.Append("<th style=\"width:100px;\">下家实收</th>");
        sb.Append("<th style=\"width:100px;\">下家未收</th></tr>");

        double VUnGeted = 0.0;      //上家未收
        double BUnGeted = 0.0;      //下家未收

        foreach (System.Data.DataRow dr in dt.Rows)
        {
            sb.Append("<tr>");
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"business",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"BuyerDepartName",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"ContractDate",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"ContractCode",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr, "HouseAddress", ""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"ConfirmEmplyee",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"CreateArchEmp",""));
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr,"VDepartName",""));
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr,"VUserName",""));
            sb.AppendFormat("<td >{0}</td>",  DatasetHelper.GetValue(dr,"VStanCommision",""));
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr,"VRealCommisionFinal","")); 
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr,"VCommisionFinal","")); 
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr,"VConsultingFinal",""));
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "VSTDiscountFinal", "")); 
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr,"VCommisionGeted","")); 
            sb.AppendFormat("<td >{0}</td>",  DatasetHelper.GetValue(dr,"VUnGeted","")); 
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"DealDate",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"ArchivementDate",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td>", DatasetHelper.GetValue(dr,"HasFollow",""));
            sb.AppendFormat("<td  rowspan=\"2\">{0}</td></tr>", DatasetHelper.GetValue(dr,"BargainCompanyName",""));
            sb.AppendFormat("<tr><td >{0}</td>", DatasetHelper.GetValue(dr,"BDepartName",""));
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "BUserName", ""));//dr["BUserName"]);
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "BStanCommision", ""));//dr["BStanCommision"]);
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "BRealCommisionFinal", ""));//dr["BRealCommision"]);
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "BCommisionFinal", ""));//dr["BCommision"]);
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "BConsultingFinal", ""));//dr["BConsulting"]);
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "BSTDiscountFinal", "")); 
            sb.AppendFormat("<td >{0}</td>", DatasetHelper.GetValue(dr, "BCommisionGeted", ""));//dr["BCommisionGeted"]);
            sb.AppendFormat("<td >{0}</td></tr>", DatasetHelper.GetValue(dr, "BUnGeted", ""));

            VUnGeted += string.IsNullOrEmpty(dr["VUnGeted"].ToString()) ? 0 : double.Parse(dr["VUnGeted"].ToString());
            BUnGeted += string.IsNullOrEmpty(dr["BUnGeted"].ToString()) ? 0 : double.Parse(dr["BUnGeted"].ToString());
        }

        sb.Append("<tr><td rowspan=\"2\">合计</td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td>");
        sb.AppendFormat("<td rowspan=\"2\"></td rowspan=\"2\"><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td>{0}</td>", VUnGeted);
        sb.AppendFormat("<td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td><td rowspan=\"2\"></td></tr><tr><td>{0}</td></tr>", BUnGeted);

        return sb.ToString();
    }
    

    private void GetList(HttpContext context)
    {
        try
        {
            StringBuilder sb = new StringBuilder();

            if (CurrentUserInfo.ContainsPermit(Constants.Permit_CommisionDetail_Search_All))
            {
                sb.Append(" (1=1) and Status=1 ");
            }
            else if (CurrentUserInfo.ContainsPermit(Constants.Permit_CommisionDetail_Search_City))
            {
                sb.AppendFormat(" (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.CityId='{0}'))) ", CurrentUserInfo.CityId.ToString());
            }
            else if (CurrentUserInfo.ContainsPermit(Constants.Permit_CommisionDetail_Search_Company))
            {
                sb.AppendFormat(" (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.companyid='{0}'))) ", CurrentUserInfo.CompanyId.ToString());
            }
            else if (CurrentUserInfo.ContainsPermit(Constants.Permit_CommisionDetail_Search_Depart))
            {
                sb.Append(" (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(");
                sb.Append(string.Format("Select 1 from t_Business_EmpSetting M WHERE t_Contract_PointAllotInfo.DepartId=M.DepartId And M.Userid= '{0}' and SearchCommisionDetail=1", CurrentUserInfo.UserId.ToString()));
                sb.Append("))) ");
            }
            else
                sb.AppendFormat(" (a.contractid in (select ContractId from t_Contract_PointAllotInfo where EmployeeId='{0}')) ", CurrentUserInfo.UserId.ToString());

            //查询条件
            //关键字
            if (!string.IsNullOrEmpty(context.Request["keyword"]))
                sb.AppendFormat(" and (ContractCode like '%{0}%' or HouseAddress like '%{0}%') ", context.Request["keyword"]);
            //归属日期
            if (!string.IsNullOrEmpty(context.Request["datetype"]))
            {
                string sdate = "";
                string edate = "";
                if (!string.IsNullOrEmpty(context.Request["ArcTimes"]))
                    sdate = context.Request["ArcTimes"];
                if (!string.IsNullOrEmpty(context.Request["ArcTimee"]))
                    edate = context.Request["ArcTimee"];

                //实收
                if (context.Request["datetype"] == "PayArchDate")
                {
                    if (!string.IsNullOrEmpty(sdate) && !string.IsNullOrEmpty(edate))
                    {
                        sb.AppendFormat(" and  EXISTS(select 1 from t_Contract_Finance_BillInfo F where a.ContractId = F.ContractId and cast(case when len(ISNULL(f.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(f.AchievementDate, '1900.1') end +'.1' as datetime) between '{0}' AND '{1}') ",
                            DateTime.Parse(sdate), DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                    }
                    else if (!string.IsNullOrEmpty(sdate))
                    {
                        sb.AppendFormat(" and  EXISTS(select 1 from t_Contract_Finance_BillInfo F where a.ContractId = F.ContractId and cast(case when len(ISNULL(f.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(f.AchievementDate, '1900.1') end +'.1' as datetime) >= '{0}' ) ",
                            DateTime.Parse(sdate));
                    }
                    else if (!string.IsNullOrEmpty(edate))
                    {
                        sb.AppendFormat(" and  EXISTS(select 1 from t_Contract_Finance_BillInfo F where a.ContractId = F.ContractId and cast(case when len(ISNULL(f.AchievementDate, '1900.1'))<1 then '1900.1' else ISNULL(f.AchievementDate, '1900.1') end +'.1' as datetime) < '{0}' ) ",
                            DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                    }
                }
                //业绩
                else
                {
                    if (!string.IsNullOrEmpty(sdate) && !string.IsNullOrEmpty(edate))
                    {
                        sb.AppendFormat(" and (ArchivementDate between '{0}' AND '{1}') ",
                            DateTime.Parse(sdate), DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                    }
                    else if (!string.IsNullOrEmpty(sdate))
                    {
                        sb.AppendFormat(" and  (ArchivementDate >= '{0}' ) ",
                            DateTime.Parse(sdate));
                    }
                    else if (!string.IsNullOrEmpty(edate))
                    {
                        sb.AppendFormat(" and  (ArchivementDate < '{0}' ) ",
                            DateTime.Parse(edate).AddMonths(1).AddDays(-1));
                    }
                }
            }
            //成交日期
            if (!string.IsNullOrEmpty(context.Request["starttime"]))
                sb.AppendFormat(" and a.ContractDate>='{0}' ", context.Request["starttime"]);
            if (!string.IsNullOrEmpty(context.Request["endtime"]))
                sb.AppendFormat(" and a.ContractDate<='{0}' ", context.Request["endtime"]);
            //归属公司
            if (!string.IsNullOrEmpty(context.Request["BargainCompanyId"]))
                sb.AppendFormat(" and BargainCompanyId='{0}' ", context.Request["BargainCompanyId"]);
            //状态
            if (!string.IsNullOrEmpty(context.Request["status"]))
            {
                if (context.Request["status"] == "10")
                {//实收截止
                    if (!string.IsNullOrEmpty(context.Request["overtime"]))
                        sb.AppendFormat(" and bArchivement=1 and ((VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal)>((dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}'))+(dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}')))) ", context.Request["overtime"]);
                    else
                        sb.Append(" and bArchivement=1 and (VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal) > (VCommisionGeted+BCommisionGeted)");
                }
                else if (context.Request["status"] == "20")
                {//实收截止
                    if (!string.IsNullOrEmpty(context.Request["overtime"]))
                        sb.AppendFormat(" and bArchivement=1 and ((VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal)<=dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}')+dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}'))  ", context.Request["overtime"]);
                    else
                        sb.Append(" and bArchivement=1 and ((VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal)<= VCommisionGeted+BCommisionGeted)");
                }
                else if (context.Request["status"] == "30")
                    sb.Append(" and bArchivement=0 ");
            }
            else
                sb.Append(" and bArchivement=1 and (VRealCommisionFinal+BRealCommisionFinal-VSTDiscountFinal-BSTDiscountFinal > VCommisionGeted+BCommisionGeted)");

            //查询5级
            Guid city = (string.IsNullOrEmpty(context.Request["cityid"])) ? Guid.Empty : new Guid(context.Request["cityid"]);
            Guid company = (string.IsNullOrEmpty(context.Request["companyid"])) ? Guid.Empty : new Guid(context.Request["companyid"]);
            Guid businessdistrict = (string.IsNullOrEmpty(context.Request["areaid"])) ? Guid.Empty : new Guid(context.Request["areaid"]);
            Guid departs = (string.IsNullOrEmpty(context.Request["departid"])) ? Guid.Empty : new Guid(context.Request["departid"]);
            Guid userId = (string.IsNullOrEmpty(context.Request["UserId"])) ? Guid.Empty : new Guid(context.Request["UserId"]);

            if (userId != Guid.Empty)
                sb.AppendFormat(" AND (a.contractid in (select ContractId from t_Contract_PointAllotInfo where EmployeeId='{0}')) ", userId);
            if (departs != Guid.Empty)
            {
                sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where DepartId='{0}'))", departs);
            }
            else if (businessdistrict != Guid.Empty)
            {
                sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo " +
                "where DepartId in (Select ChildId from v_Hr_Struct_DepartTreeRelation where ParentId ='{0}')))",
                    businessdistrict);
            }
            else if (company != Guid.Empty)
            {
                sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.companyid='{0}')))", company);
            }
            else if (city != Guid.Empty)
            {
                sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.CityId='{0}'))) ", city);
            }
            else
            {
                sb.AppendFormat(" and (a.contractid in (select contractid from t_Contract_PointAllotInfo where EXISTS(select 1 from v_Hr_DepartmentInfo v where v.departid=t_Contract_PointAllotInfo.departid and v.companyid='{0}'))) ", CurrentUserInfo.CompanyId);
            }
            
            
            
            //条件
            string strWhere = sb.ToString();
            //页码
            int strPageIndex = (string.IsNullOrEmpty(context.Request["pageindex"])) ? 1 : int.Parse(context.Request["pageindex"]) + 1;//页码
            //页显示行数
            int strPageSize = (string.IsNullOrEmpty(context.Request["pagesize"])) ? 30 : int.Parse(context.Request["pagesize"]);//页记录数
            //欲选择字段列表 
            string FieldList = "ContractId,business,BuyerDepartName,ContractDate,ContractCode,ConfirmEmplyee,CreateArchEmp,HouseAddress,VDepartName,"
                                + "BDepartName,VUserName,BUserName,V_Points,B_Points,VStanCommision,BStanCommision,VRealCommisionFinal,BRealCommisionFinal,VCommisionFinal,BCommisionFinal,VConsultingFinal,BConsultingFinal,"
                                + "VCommisionGeted,BCommisionGeted,VUnGeted,BUnGeted,DealDate,ArchivementDate,HasFollow,BargainCompanyName,VSTDiscountFinal,BSTDiscountFinal";

            StringBuilder sbTableFieldList = new StringBuilder();
            sbTableFieldList.Append("a.ContractId,dbo.FN_GetBussinessByDepartID(BuyerDepartId) as business,BuyerDepartName,");
            sbTableFieldList.Append("CONVERT(Varchar(10),a.ContractDate,102) as ContractDate,ContractCode,ConfirmEmplyee,CreateArchEmp,HouseAddress,VSTDiscountFinal,BSTDiscountFinal,");
            sbTableFieldList.Append("dbo.f_get_contract_pointallot('Depart',a.ContractId,'10') AS VDepartName," +
                    "dbo.f_get_contract_pointallot('Employee',a.ContractId,'10') as VUserName," +
                    "dbo.f_get_contract_pointallot('Point',a.ContractId,'10') as V_Points," +
                    "dbo.f_get_contract_pointallot('Depart',a.ContractId,'20') AS BDepartName," +
                    "dbo.f_get_contract_pointallot('Employee',a.ContractId,'20') as BUserName," +
                    "dbo.f_get_contract_pointallot('Point',a.ContractId,'20') as B_Points,");

            sbTableFieldList.Append("VStanCommision,BStanCommision,VRealCommisionFinal,BRealCommisionFinal,");
            sbTableFieldList.Append("VCommisionFinal,BCommisionFinal,VConsultingFinal,BConsultingFinal,");
            sbTableFieldList.AppendFormat("dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}') as VCommisionGeted,dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}') as BCommisionGeted,(VRealCommisionFinal-VSTDiscountFinal-dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}')) as VUnGeted,", string.IsNullOrEmpty(context.Request["overtime"]) ? "" : context.Request["overtime"]);
            sbTableFieldList.AppendFormat("(BRealCommisionFinal-BSTDiscountFinal-dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}')) as BUnGeted,", string.IsNullOrEmpty(context.Request["overtime"]) ? "" : context.Request["overtime"]);
            sbTableFieldList.Append("DATEDIFF(d,DealDate,getdate()) as DealDate,");
            sbTableFieldList.Append("Convert(varchar(10),ArchivementDate,102) as ArchivementDate, ");
            sbTableFieldList.Append("dbo.f_getRemarkInfo(a.ContractId) as HasFollow,BargainCompanyName");

            //所要查询的表名
            string TableName = "t_Contract_BaseInfo a inner join t_Contract_CommisionInfo b On A.ContractId = b.ContractId left join t_Contract_Infomation c on a.contractId = c.contractId ";
            //排序
            string strOrderField = "a.ContractDate desc";
            if (!string.IsNullOrEmpty(context.Request["sort"]))
                strOrderField = context.Request["sort"];
            
            //根据sql执行查询
            DataSet ds = Chenxin.Lib.Business.DAL.t_Contract_BaseInfo.GetList(strPageIndex, strPageSize, strOrderField, TableName, strWhere, FieldList, sbTableFieldList.ToString(), true);

            //*************获取总合计数*****************
            string strSql = string.Format("select sum(VRealCommisionFinal-VSTDiscountFinal-dbo.f_getCommisionGeted(a.contractid,'上家佣金','{0}')) as VUnGeted,sum(BRealCommisionFinal-BSTDiscountFinal-dbo.f_getCommisionGeted(a.contractid,'下家佣金','{0}')) as BUnGeted  from ", string.IsNullOrEmpty(context.Request["overtime"]) ? "" : context.Request["overtime"]) + TableName + " where " + strWhere.ToString();
            DataSet ds_count = Chenxin.Lib.Business.DAL.v_Contract_CommisionDetailInfo.GetList(strSql);
            System.Data.DataRow dr = ds.Tables[0].NewRow();
            dr["business"] = "总合计";
            dr["VUnGeted"] = ds_count.Tables[0].Rows[0]["VUnGeted"];
            dr["BUnGeted"] = ds_count.Tables[0].Rows[0]["BUnGeted"];
            ds.Tables[0].Rows.Add(dr);
            //*****************************************

            context.Response.Write(string.Format("{{success:1,msg:'',data:{{count:{0},{1}}}}}", ds.Tables[1].Rows[0][0], Chenxin.Lib.Utility.JsonHelp.Convert2Json(ds.Tables[0]))); 
        }
        catch (Exception ex)
        {
            context.Response.Write(retExecResult(Constants.ExecResult.Error, ex.Message, ""));
        }
    }
}